package merlin.dao;

import java.util.*;
import java.sql.*;

import merlin.beans.EmailDataBean;

public class WebsiteEnquiriesDAO implements java.io.Serializable{

	Connection con;

	public WebsiteEnquiriesDAO(Connection connection){
		con = connection;
	}

	public int addWebsiteEnquiry(String strFirstName,String strSurname,String strCompanyName,String strEMailAddress,String strTelephone)throws SQLException{

		Statement stmt = con.createStatement();
   		ResultSet rs = stmt.executeQuery("SELECT UNIQUE FROM NSD.WEBSITE_ENQUIRIES");
		rs.next();
		int newID = rs.getInt(1);
		rs.close();
		stmt.close();

		PreparedStatement pstmt = con.prepareStatement("INSERT INTO WEBSITE_ENQUIRIES(WEBSITE_ENQUIRIES_ID,FIRST_NAME,SURNAME,COMPANY_NAME,EMAIL_ADDRESS,DATE,TELEPHONE_NUMBER)VALUES(?,?,?,?,?,CURRENT_DATE,?)");
		pstmt.setInt(1,newID);
		pstmt.setString(2,strFirstName);
		pstmt.setString(3,strSurname);
		pstmt.setString(4,strCompanyName);
		pstmt.setString(5,strEMailAddress);
		pstmt.setString(6,strTelephone);
		int updateCols = pstmt.executeUpdate();
		pstmt.close();
		return newID;
	}

	public int storeLandingPageVisitDetailsFromEmailLink(int reportNumber, String companyNumber)throws SQLException{
		// Check if the provided combination of reportNumber and companyNumber already exists in the table or not.
		// If present, return the primary key value of the record, else add the record and return the primary key 
		// of the new record.
		int recordId = 0;
		boolean previousRecordExists = false;
		
		PreparedStatement pstmtSel = con.prepareStatement("SELECT MV_ID FROM NSD.MSAL_VISITORS WHERE MV_COMPANY_NUMBER = ? AND MV_REPORT_NUMBER = ?");
		pstmtSel.setString(1, companyNumber);
		pstmtSel.setInt(2, reportNumber);
		ResultSet rsSel = pstmtSel.executeQuery();
		if(rsSel.next()){
			recordId = rsSel.getInt(1);
			previousRecordExists = true;
			System.out.println("The entry for for the report no " + reportNumber + " & company no " + companyNumber + " already exists.");
		}else{
			System.out.println("The entry for for the report no " + reportNumber + " & company no " + companyNumber + " DOES NOT exist.");
		}
		rsSel.close();
		pstmtSel.close();
		
		if(!previousRecordExists){
			Statement stmt = con.createStatement();
	   		ResultSet rs = stmt.executeQuery("SELECT UNIQUE FROM NSD.MSAL_VISITORS");
			rs.next();
			recordId = rs.getInt(1);
			rs.close();
			stmt.close();
			
			PreparedStatement pstmtIns = con.prepareStatement("INSERT INTO NSD.MSAL_VISITORS(MV_ID, MV_COMPANY_NUMBER, MV_REPORT_NUMBER, MV_DATE_COLLECTED) VALUES(?, ?, ?, CURRENT_DATE)");
			pstmtIns.setInt(1, recordId);
			pstmtIns.setString(2, companyNumber);
			pstmtIns.setInt(3, reportNumber);
			int updateCols = pstmtIns.executeUpdate();
			pstmtIns.close();
		}
		
		return recordId;
	}
	
	public int addEmailAddressForFreeReport(EmailDataBean bean)throws SQLException{
		
		// Check if the provided combination of emailId, reportId and companyId already exists in the table or not.
		// If present, return the primary key value of the record, else add the record and return the primary key 
		// of the new record.
		int recordId = 0;
		boolean previousRecordExists = false;
		
		PreparedStatement pstmtSel = con.prepareStatement("SELECT ED_ID FROM NSD.EMAIL_DATA WHERE ED_COMPANY_NUMBER = ? AND ED_REPORT_NUMBER = ? AND ED_EMAIL = ?");
		pstmtSel.setString(1, bean.getCompanyNumber());
		pstmtSel.setInt(2, bean.getReportNumber());
		pstmtSel.setString(3, bean.getEmailAddress());		
		ResultSet rsSel = pstmtSel.executeQuery();
		if(rsSel.next()){
			recordId = rsSel.getInt(1);
			previousRecordExists = true;
			System.out.println("The entry for emailId " + bean.getEmailAddress() + " for the report no " + bean.getReportNumber() + " & company no " + bean.getCompanyNumber() + " already exists.");
		}else{
			System.out.println("The entry for emailId " + bean.getEmailAddress() + " for the report no " + bean.getReportNumber() + " & company no " + bean.getCompanyNumber() + " DOES NOT exist.");
		}
		rsSel.close();
		pstmtSel.close();
		
		if(!previousRecordExists){
			Statement stmt = con.createStatement();
	   		ResultSet rs = stmt.executeQuery("SELECT UNIQUE FROM NSD.EMAIL_DATA");
			rs.next();
			recordId = rs.getInt(1);
			rs.close();
			stmt.close();
			
			PreparedStatement pstmtIns = con.prepareStatement("INSERT INTO NSD.EMAIL_DATA(ED_ID, ED_COMPANY_NUMBER, ED_REPORT_NUMBER, ED_EMAIL, ED_VERIFIED, ED_DATE_COLLECTED) VALUES(?,?,?,?,?, CURRENT_DATE)");
			pstmtIns.setInt(1, recordId);
			pstmtIns.setString(2, bean.getCompanyNumber());
			pstmtIns.setInt(3, bean.getReportNumber());
			pstmtIns.setString(4, bean.getEmailAddress());
			pstmtIns.setInt(5, bean.getVerified());
			int updateCols = pstmtIns.executeUpdate();
			pstmtIns.close();
		}
		
		return recordId;
	}
	
	public EmailDataBean selectEmailDataForMSALFreeReportDownloadByRecordId(int recordId) throws SQLException{
		EmailDataBean bean = null;
		PreparedStatement pstmt = con.prepareStatement("SELECT ED_REPORT_NUMBER FROM NSD.EMAIL_DATA WHERE ED_ID = ?");
		pstmt.setInt(1, recordId);
		ResultSet rs = pstmt.executeQuery();
		
		if(rs.next()){
			bean = new EmailDataBean();
			bean.setReportNumber(rs.getInt(1));
		}
		rs.close();
		pstmt.close();
		
		return bean;
	}
	
	public void updateMSALFreeReportDownloadFlagByRecordId(int recordId) throws SQLException{
		// The ED_DATE_VERIFIED field is to have the date on which the user downloaded the report for the first time.
		PreparedStatement pstmt = con.prepareStatement("UPDATE NSD.EMAIL_DATA SET ED_VERIFIED = 1, ED_DATE_VERIFIED = coalesce(ED_DATE_VERIFIED, CURRENT_DATE) WHERE ED_ID = ?");
		pstmt.setInt(1, recordId);
		int updateCols = pstmt.executeUpdate();
		pstmt.close();
	}
	
	/*
	public WebsiteEnquiriesBean selectWebsiteEnquiriesByPK(WebsiteEnquiriesBean bean)throws SQLException{

		PreparedStatement pstmt = con.prepareStatement("SELECT WEBSITE_ENQUIRIES_ID,FIRST_NAME,SURNAME,COMPANY_NAME,EMAIL_ADDRESS,DATE FROM WEBSITE_ENQUIRIES WHERE WEBSITE_ENQUIRIES_ID = ? ");
		pstmt.setInt(1,bean.getWebsiteEnquiriesId());

		WebsiteEnquiriesBean bn = null;
		ResultSet rs = pstmt.executeQuery();
		if(rs.next()){
			bn = new WebsiteEnquiriesBean();
			bn.setWebsiteEnquiriesId(rs.getInt("WEBSITE_ENQUIRIES_ID"));
			bn.setFirstName(rs.getString("FIRST_NAME"));
			bn.setSurname(rs.getString("SURNAME"));
			bn.setCompanyName(rs.getString("COMPANY_NAME"));
			bn.setEmailAddress(rs.getString("EMAIL_ADDRESS"));
			bn.setDate(rs.getDate("DATE"));
		}
		rs.close();
		return bn;
	}

	public DbResultSet selectWebsiteEnquiriesRecords(String whereClause,String orderByClause)throws SQLException{

		ArrayList records = new ArrayList();
		String sql ="SELECT WEBSITE_ENQUIRIES_ID,FIRST_NAME,SURNAME,COMPANY_NAME,EMAIL_ADDRESS,DATE FROM WEBSITE_ENQUIRIES";
		if(whereClause !=null && !whereClause.equals(""))sql += " WHERE " + whereClause;
		if(orderByClause !=null && !orderByClause.equals(""))sql += " ORDER BY " + orderByClause;
		Statement stmt =  con.createStatement();
		ResultSet rs =  stmt.executeQuery(sql);
		while(rs.next()){
			WebsiteEnquiriesBean bn = new WebsiteEnquiriesBean(rs.getInt("WEBSITE_ENQUIRIES_ID"),rs.getString("FIRST_NAME"),rs.getString("SURNAME"),rs.getString("COMPANY_NAME"),rs.getString("EMAIL_ADDRESS"),rs.getDate("DATE"));
			records.add(bn);
		}
		rs.close();
		stmt.close();
		if(!records.isEmpty())return new DbResultSet(records,records.size()); else return new DbResultSet();
	}

	public int updateWebsiteEnquiries(WebsiteEnquiriesBean bean)throws SQLException{
		PreparedStatement pstmt = con.prepareStatement("UPDATE WEBSITE_ENQUIRIES SET FIRST_NAME = ? ,SURNAME = ? ,COMPANY_NAME = ? ,EMAIL_ADDRESS = ? ,DATE = ?  WHERE WEBSITE_ENQUIRIES_ID = ?");
		pstmt.setString(1,bean.getFirstName());
		pstmt.setString(2,bean.getSurname());
		pstmt.setString(3,bean.getCompanyName());
		pstmt.setString(4,bean.getEmailAddress());
		pstmt.setDate(5,bean.getDate());
		pstmt.setInt(6,bean.getWebsiteEnquiriesId());
		int updateCols = pstmt.executeUpdate();
		pstmt.close();
		return updateCols;
	}

	public int deleteWebsiteEnquiries(WebsiteEnquiriesBean bean)throws SQLException{
		PreparedStatement pstmt = con.prepareStatement("DELETE FROM WEBSITE_ENQUIRIES WHERE WEBSITE_ENQUIRIES_ID = ?");
		pstmt.setInt(1,bean.getWebsiteEnquiriesId());
		int updateCols = pstmt.executeUpdate();
		pstmt.close();
		return updateCols;
	}
	*/
}